SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

DECLARE @AlteredFlag BIT
SELECT @AlteredFlag	= 0;

IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = 'dbo'
				AND TABLE_NAME = 'Group'
				AND TABLE_TYPE = 'BASE TABLE' )
	-------------------------------------------------------------------------------
	--Table Already Exists - Execute ALTER Statements
	-------------------------------------------------------------------------------
	BEGIN
		/*
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Group' AND COLUMN_NAME = 'StartDate' )
		BEGIN
			ALTER TABLE dbo.Group ADD StartDate SMALLDATETIME NULL;
			PRINT 'dbo.Group.StartDate column added.';
			SET @AlteredFlag = 1;
		END
		*/
		IF ( @AlteredFlag = 1 )
			BEGIN
				PRINT 'Table Group altered with latest changes.';		
				PRINT 'Table Group successfully upgraded.';
			END
		ELSE
			BEGIN
				PRINT 'Table Group requires no changes.';
			END
		END
ELSE
	-------------------------------------------------------------------------------
	--Table Does Not Exist - Create the Table
	-------------------------------------------------------------------------------
	BEGIN
		CREATE TABLE [dbo].[Group](
			[ID] [int] IDENTITY(1,1) NOT NULL,
			[Name] [nvarchar](150) NOT NULL,
		 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]
	-------------------------------------------------------------------------------
	--	END OF CREATE STATEMENTS.
	--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
	-------------------------------------------------------------------------------
	-- Make sure object was created successfully.
	IF EXISTS (	SELECT *
				FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_SCHEMA = 'dbo'
					AND TABLE_NAME = 'Group'
					AND TABLE_TYPE = 'BASE TABLE' )


		BEGIN
			PRINT 'Table Group created successfully.';	
		END
	ELSE
		BEGIN
			PRINT 'ERROR: Failed to create Table Group.';
		END
	END
GO



